/*
   Copyright (c) 2025, Oracle and/or its affiliates.

   This program is free software; you can redistribute it and/or modify
   it under the terms of the GNU General Public License, version 2.0,
   as published by the Free Software Foundation.

   This program is designed to work with certain software (including
   but not limited to OpenSSL) that is licensed under separate terms,
   as designated in a particular file or component or in included license
   documentation.  The authors of MySQL hereby grant you an additional
   permission to link the program and your derivative works with the
   separately licensed software that they have either included with
   the program or referenced in the documentation.

   This program is distributed in the hope that it will be useful,
   but WITHOUT ANY WARRANTY; without even the implied warranty of
   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
   GNU General Public License, version 2.0, for more details.

   You should have received a copy of the GNU General Public License
   along with this program; if not, write to the Free Software
   Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301  USA
*/

package testsuite.clusterj;

import java.util.ArrayList;
import java.util.List;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Set;
import java.util.Comparator;
import java.util.TreeSet;

import testsuite.clusterj.model.DateAsPkSqlDateTypes;
import testsuite.clusterj.model.IdBase;
import testsuite.clusterj.model.DateIdBase;

/** Test that Dates can be read and written.
 * case 1: Write using JDBC, read using NDB.
 * case 2: Write using NDB, read using JDBC.
 * Schema
 *
drop table if exists datetypes_pk;
create table datetypes_pk (
 id int not null,
 pk_key_date not null date,

 date_null_hash date,
 date_null_btree date,
 date_null_both date,
 date_null_none date,

 date_not_null_hash date,
 date_not_null_btree date,
 date_not_null_both date,
 date_not_null_none date,

 PRIMARY KEY(id, pk_key_date)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1;

create unique index idx_date_null_hash using hash on datetypes_pk(date_null_hash);
create index idx_date_null_btree on datetypes_pk(date_null_btree);
create unique index idx_date_null_both on datetypes_pk(date_null_both);

create unique index idx_date_not_null_hash using hash on datetypes_pk(date_not_null_hash);
create index idx_date_not_null_btree on datetypes_pk(date_not_null_btree);
create unique index idx_date_not_null_both on datetypes_pk(date_not_null_both);

 */
public class DateAsPkSqlDateTypesTest extends AbstractClusterJModelTest {

    static int NUMBER_OF_INSTANCES = 10;

    /** The instances used in the tests, generated by generateInstances */
    protected List<DateIdBase> date_instances = new ArrayList<DateIdBase>();

    protected void consistencyCheck(DateIdBase instance) {}

    @Override
    protected boolean getDebug() {
        return false;
    }

    @Override
    protected int getNumberOfInstances() {
        return NUMBER_OF_INSTANCES;
    }

    @Override
    protected String getTableName() {
        return "datetypes_pk";
    }

    /** Subclasses override this method to provide the model class for the test */
    Class<? extends DateIdBase> getDateModelClass() {
        return DateAsPkSqlDateTypes.class;
    }

    /** Subclasses override this method to provide values for rows (i) and columns (j) */
    @Override
    protected Object getColumnValue(int i, int j) {
        return new Date(getMillisFor(1980, i, j + 1));
    }

    public void testWriteJDBCReadNDB() {
        writeJDBCreadNDB();
        failOnError();
    }

    public void testWriteNDBReadNDB() {
        writeNDBreadNDB();
        failOnError();
    }

    public void testWriteJDBCReadJDBC() {
        writeJDBCreadJDBC();
        failOnError();
    }

    public void testWriteNDBReadJDBC() {
        writeNDBreadJDBC();
        failOnError();
    }

    @Override
    public void localSetUp() {
        createSessionFactory();
        session = sessionFactory.getSession();
        setAutoCommit(connection, false);
        try {
            session.newInstance(getDateModelClass());
            runTest = true;
        } catch (Exception e) {
            System.out.println("Ignoring test; no model class " + getDateModelClass().getName());
            runTest = false;
        }

        if (getDateModelClass() != null && getCleanupAfterTest()) {
            addTearDownClasses(getDateModelClass());
        }
    }

    /** Write data via JDBC and read back the data via NDB */
    @Override
    protected void writeJDBCreadNDB() {
        if (!runTest) return;
        generateInstances(getColumnDateDescriptors());
        removeAll(getDateModelClass());
        List<Object[]> result = null;
        writeToJDBC(columnDescriptors, date_instances);
        result = readFromNDB(columnDescriptors);
        verify("writeJDBCreadNDB", getExpected(), result);
    }

    /** Write data via JDBC and read back the data via JDBC */
    @Override
    protected void writeJDBCreadJDBC() {
        if (!runTest) return;
        generateInstances(getColumnDateDescriptors());
        removeAll(getDateModelClass());
        List<Object[]> result = null;
        writeToJDBC(columnDescriptors, date_instances);
        result = readFromJDBC(columnDescriptors);
        verify("writeJDBCreadJDBC", getExpected(), result);
    }

    /** Write data via NDB and read back the data via NDB */
    @Override
    protected void writeNDBreadNDB() {
        if (!runTest) return;
        generateInstances(getColumnDateDescriptors());
        removeAll(getDateModelClass());
        List<Object[]> result = null;
        writeToNDB(date_instances);
        result = readFromNDB(columnDescriptors);
        verify("writeNDBreadNDB", getExpected(), result);
    }

    /** Write data via NDB and read back the data via JDBC */
    @Override
    protected void writeNDBreadJDBC() {
        if (!runTest) return;
        generateInstances(getColumnDateDescriptors());
        removeAll(getDateModelClass());
        List<Object[]> result = null;
        writeToNDB(date_instances);
        result = readFromJDBC(columnDescriptors);
        verify("writeNDBreadJDBC", getExpected(), result);
    }

    /** Write data via NDB */
    protected void writeToNDB(List<DateIdBase> instances) {
        session.currentTransaction().begin();
        session.makePersistentAll(instances);
        session.currentTransaction().commit();
    }

    /** Read data via NDB */
    protected List<Object[]> readFromNDB(ColumnDateDescriptor[] columnDescriptors) {
        Class<? extends DateIdBase> modelClass = getDateModelClass();
        List<Object[]> result = new ArrayList<Object[]>();
        session.currentTransaction().begin();
        for (int i = 0; i < getNumberOfInstances() ; ++i) {
            Object key[] = new Object[2];
            key[0] = convertToKey(i);
            key[1] = getColumnValue(i, 0);
            DateIdBase instance = session.find(modelClass, key);
            if (instance != null) {
                Object[] row = createDateRow(columnDescriptors, instance);
                result.add(row);
            }
        }
        session.currentTransaction().commit();
        if (debug) System.out.println("readFromNDB: " + dumpListOfObjectArray(result));
        return result;
    }

    /** Read data via JDBC */
    protected List<Object[]> queryJDBC(ColumnDateDescriptor[] columnDescriptors,
            String conditions, Object[] parameters) {
        getConnection();
        String tableName = getTableName();
        List<Object[]> result = new ArrayList<Object[]>();
        StringBuffer buffer = new StringBuffer("SELECT id");
        for (ColumnDateDescriptor columnDescriptor: columnDescriptors) {
            buffer.append(", ");
            buffer.append(columnDescriptor.getColumnName());
        }
        buffer.append(" FROM ");
        buffer.append(tableName);
        buffer.append(" WHERE ");
        buffer.append(conditions);
        String statement = buffer.toString();
        if (debug) System.out.println(statement);
        PreparedStatement preparedStatement = null;
        try {
            int p = 1;
            preparedStatement = connection.prepareStatement(statement);
            for (Object parameter: parameters) {
                preparedStatement.setObject(p++, parameter);
            }
            ResultSet rs = preparedStatement.executeQuery();
            while (rs.next()) {
                Object[] row = new Object[columnDescriptors.length + 1];
                int j = 1;
                row[0] = rs.getInt(1);
                for (ColumnDateDescriptor columnDescriptor: columnDescriptors) {
                    row[j] = columnDescriptor.getResultSetValue(rs, j + 1);
                    ++j;
                }
                result.add(row);
            }
            if (!connection.getAutoCommit()) {
                connection.commit();
            }
        } catch (SQLException e) {
            throw new RuntimeException("Failed to read " + tableName, e);
        }
        if (debug) System.out.println("readFromJDBC: " + dumpObjectArray(result));
        return result;
    }

    /** Write data to JDBC. */
    protected void writeToJDBC(ColumnDateDescriptor[] columnDescriptors, List<DateIdBase> instances) {
        String tableName = getTableName();
        StringBuffer buffer = new StringBuffer("INSERT INTO ");
        buffer.append(tableName);
        buffer.append(" (id");
        for (ColumnDateDescriptor columnDescriptor: columnDescriptors) {
            buffer.append(", ");
            buffer.append(columnDescriptor.getColumnName());
        }
        buffer.append(") VALUES (?");
        for (ColumnDateDescriptor columnDescriptor: columnDescriptors) {
            buffer.append(", ?");
        }
        buffer.append(")");
        String statement = buffer.toString();
        if (debug) System.out.println(statement);

        PreparedStatement preparedStatement = null;
        int i = 0;
        try {
            preparedStatement = connection.prepareStatement(statement);
            if (debug) System.out.println(preparedStatement.toString());
            for (i = 0; i < instances.size(); ++i) {
                DateIdBase instance = instances.get(i);
                preparedStatement.setInt(1, instance.getId());
                int j = 2;
                for (ColumnDateDescriptor columnDescriptor: columnDescriptors) {
                    Object value = columnDescriptor.getFieldValue(instance);
                    columnDescriptor.setPreparedStatementValue(preparedStatement, j++, value);
                    if (debug) System.out.println("writeToJDBC set column: " + columnDescriptor.getColumnName() + " to value: " + value);
                }
                preparedStatement.execute();
            }
            if (!connection.getAutoCommit()) {
                connection.commit();
            }
        } catch (SQLException e) {
            throw new RuntimeException("Failed to insert " + tableName + " at instance " + i, e);
        }
    }

    /** Read data via JDBC ordered by id */
    protected List<Object[]> readFromJDBC(ColumnDateDescriptor[] columnDescriptors) {
        String tableName = getTableName();
        List<Object[]> result = new ArrayList<Object[]>();
        Set<Object[]> rows = new TreeSet<Object[]>(new Comparator<Object[]>(){
            public int compare(Object[] me, Object[] other) {
                return ((Integer)me[0]) - ((Integer)other[0]);
            }
        });
        StringBuffer buffer = new StringBuffer("SELECT id");
        for (ColumnDateDescriptor columnDescriptor: columnDescriptors) {
            buffer.append(", ");
            buffer.append(columnDescriptor.getColumnName());
        }
        buffer.append(" FROM ");
        buffer.append(tableName);
        String statement = buffer.toString();
        if (debug) System.out.println(statement);
        PreparedStatement preparedStatement = null;
        int i = 0;
        try {
            preparedStatement = connection.prepareStatement(statement);
            ResultSet rs = preparedStatement.executeQuery();
            while (rs.next()) {
                Object[] row = new Object[columnDescriptors.length + 1];
                int j = 1;
                row[0] = rs.getInt(1);
                for (ColumnDateDescriptor columnDescriptor: columnDescriptors) {
                    row[j] = columnDescriptor.getResultSetValue(rs, j + 1);
                    ++j;
                }
                ++i;
                rows.add(row);
            }
            if (!connection.getAutoCommit()) {
                connection.commit();
            }
        } catch (SQLException e) {
            throw new RuntimeException("Failed to read " + tableName + " at instance " + i, e);
        }
        result = new ArrayList<Object[]>(rows);
        if (debug) System.out.println("readFromJDBC: " + dumpListOfObjectArray(result));
        return result;
    }

    protected DateIdBase getNewDateInstance(Class<? extends DateIdBase> modelClass) {
        DateIdBase instance;
        instance = session.newInstance(modelClass);
        return instance;
    }

    /** Generated instances to persist. When using JDBC, the data is obtained from the instance
     * via the column descriptors. As a side effect (!) create the list of expected results from read.
     * Specialize this method since we add a Date column to the primary key
     * @param columnDescriptors the column descriptors
     * @return the generated instances
     */
    protected void generateInstances(ColumnDateDescriptor[] columnDescriptors) {
        Class<? extends DateIdBase> modelClass = getDateModelClass();
        expected = new ArrayList<Object[]>();
        date_instances = new ArrayList<DateIdBase>();
        DateIdBase instance = null;
        int numberOfInstances = getNumberOfInstances();
        for (int i = 0; i < numberOfInstances; ++i) {
            // create the instance
            instance = getNewDateInstance(modelClass);
            instance.setId(i);
            Object key_date = getColumnValue(i, 0);
            instance.setPkKeyDate((Date)key_date);
            // create the expected result row, skip date PK key here
            int j = 0;
            for (ColumnDateDescriptor columnDescriptor: columnDescriptors) {
                Object value = getColumnValue(i, j);
                if (debug) System.out.println("generateInstances set field " + columnDescriptor.getColumnName()
                        + " to value "  + value);
                // set the column value in the instance
                columnDescriptor.setFieldValue(instance, value);
                // check that the value was set correctly
                Object actual = columnDescriptor.getFieldValue(instance);
                errorIfNotEqual("generateInstances value mismatch for " + columnDescriptor.getColumnName(),
                        dump(value), dump(actual));
                ++j;
            }
            date_instances.add(instance);
            // set the column values in the expected result
            Object[] expectedRow = createDateRow(columnDescriptors, instance);
            expected.add(expectedRow);
        }
        if (debug) System.out.println("Created " + date_instances.size() + " instances of " + modelClass.getName());
    }

    protected Object[] createDateRow(ColumnDateDescriptor[] columnDescriptors,
                                     DateIdBase instance) {
        Object[] row = new Object[columnDescriptors.length + 1];
        row[0] = instance.getId();
        int j = 1;
        for (ColumnDateDescriptor columnDescriptor: columnDescriptors) {
            row[j++] = columnDescriptor.getFieldValue(instance);
        }
        return row;
    }

    /** This class describes columns and fields for a table and model class.
     * A subclass will instantiate instances of this class and provide handlers to
     * read and write fields and columns via methods defined in the instance handler.
     */
    protected static class ColumnDateDescriptor {

        private String columnName;

        protected InstanceDateHandler instanceHandler;

        public String getColumnName() {
            return columnName;
        }

        public Object getResultSetValue(ResultSet rs, int j) throws SQLException {
            return instanceHandler.getResultSetValue(rs, j);
        }

        public Object getFieldValue(DateIdBase instance) {
            return instanceHandler.getFieldValue(instance);
        }

        public void setFieldValue(DateIdBase instance, Object value) {
            this.instanceHandler.setFieldValue(instance, value);
        }

        public void setPreparedStatementValue(PreparedStatement preparedStatement, int j, Object value)
                throws SQLException {
            instanceHandler.setPreparedStatementValue(preparedStatement, j, value);
        }

        public ColumnDateDescriptor(String name, InstanceDateHandler instanceHandler) {
            this.columnName = name;
            this.instanceHandler = instanceHandler;
        }
    }

    protected interface InstanceDateHandler {
        void setFieldValue(DateIdBase instance, Object value);
        Object getResultSetValue(ResultSet rs, int j)
                throws SQLException;
        Object getFieldValue(DateIdBase instance);
        public void setPreparedStatementValue(PreparedStatement preparedStatement, int j, Object value)
                throws SQLException;
    }


    static ColumnDateDescriptor pk_key_date = new ColumnDateDescriptor
            ("pk_key_date", new InstanceDateHandler() {
        public void setFieldValue(DateIdBase instance, Object value) {
            ((DateAsPkSqlDateTypes)instance).setPkKeyDate((Date)value);
        }
        public Object getFieldValue(DateIdBase instance) {
            return ((DateAsPkSqlDateTypes)instance).getPkKeyDate();
        }
        public void setPreparedStatementValue(PreparedStatement preparedStatement, int j, Object value)
                throws SQLException {
            preparedStatement.setDate(j, (Date)value);
        }
        public Object getResultSetValue(ResultSet rs, int j) throws SQLException {
            return rs.getDate(j);
        }
    });

    static ColumnDateDescriptor not_null_hash = new ColumnDateDescriptor
            ("date_not_null_hash", new InstanceDateHandler() {
        public void setFieldValue(DateIdBase instance, Object value) {
            ((DateAsPkSqlDateTypes)instance).setDate_not_null_hash((Date)value);
        }
        public Object getFieldValue(DateIdBase instance) {
            return ((DateAsPkSqlDateTypes)instance).getDate_not_null_hash();
        }
        public void setPreparedStatementValue(PreparedStatement preparedStatement, int j, Object value)
                throws SQLException {
            preparedStatement.setDate(j, (Date)value);
        }
        public Object getResultSetValue(ResultSet rs, int j) throws SQLException {
            return rs.getDate(j);
        }
    });

    static ColumnDateDescriptor not_null_btree = new ColumnDateDescriptor
            ("date_not_null_btree", new InstanceDateHandler() {
        public void setFieldValue(DateIdBase instance, Object value) {
            ((DateAsPkSqlDateTypes)instance).setDate_not_null_btree((Date)value);
        }
        public Object getFieldValue(DateIdBase instance) {
            return ((DateAsPkSqlDateTypes)instance).getDate_not_null_btree();
        }
        public void setPreparedStatementValue(PreparedStatement preparedStatement, int j, Object value)
                throws SQLException {
            preparedStatement.setDate(j, (Date)value);
        }
        public Object getResultSetValue(ResultSet rs, int j) throws SQLException {
            return rs.getDate(j);
        }
    });
    static ColumnDateDescriptor not_null_both = new ColumnDateDescriptor
            ("date_not_null_both", new InstanceDateHandler() {
        public void setFieldValue(DateIdBase instance, Object value) {
            ((DateAsPkSqlDateTypes)instance).setDate_not_null_both((Date)value);
        }
        public Date getFieldValue(DateIdBase instance) {
            return ((DateAsPkSqlDateTypes)instance).getDate_not_null_both();
        }
        public void setPreparedStatementValue(PreparedStatement preparedStatement, int j, Object value)
                throws SQLException {
            preparedStatement.setDate(j, (Date)value);
        }
        public Object getResultSetValue(ResultSet rs, int j) throws SQLException {
            return rs.getDate(j);
        }
    });
    static ColumnDateDescriptor not_null_none = new ColumnDateDescriptor
            ("date_not_null_none", new InstanceDateHandler() {
        public void setFieldValue(DateIdBase instance, Object value) {
            ((DateAsPkSqlDateTypes)instance).setDate_not_null_none((Date)value);
        }
        public Date getFieldValue(DateIdBase instance) {
            return ((DateAsPkSqlDateTypes)instance).getDate_not_null_none();
        }
        public void setPreparedStatementValue(PreparedStatement preparedStatement, int j, Object value)
                throws SQLException {
            preparedStatement.setDate(j, (Date)value);
        }
        public Object getResultSetValue(ResultSet rs, int j) throws SQLException {
            return rs.getDate(j);
        }
    });

    protected static ColumnDateDescriptor[] columnDescriptors = new ColumnDateDescriptor[] {
            pk_key_date,
            not_null_hash,
            not_null_btree,
            not_null_both,
            not_null_none
        };

    protected ColumnDateDescriptor[] getColumnDateDescriptors() {
        return columnDescriptors;
    }
}
